--------------------------------------------------------
--  File created - Saturday-December-03-2011   
--------------------------------------------------------
  DROP TABLE "HDCP1"."CAREGIVER" cascade constraints;
  DROP TABLE "HDCP1"."CASEWORKER" cascade constraints;
  DROP TABLE "HDCP1"."CLIENT" cascade constraints;
  DROP TABLE "HDCP1"."CLIENTCONTACT" cascade constraints;
  DROP TABLE "HDCP1"."CREDENTIAL" cascade constraints;
  DROP TABLE "HDCP1"."INSURED" cascade constraints;
  DROP TABLE "HDCP1"."INVOICE" cascade constraints;
  DROP TABLE "HDCP1"."PAYMENT" cascade constraints;
  DROP TABLE "HDCP1"."SCHEDULEDVISIT" cascade constraints;
  DROP TABLE "HDCP1"."STAFFCONTACT" cascade constraints;
  DROP TABLE "HDCP1"."STAFFCREDENTIAL" cascade constraints;
  DROP SEQUENCE "HDCP1"."CAREGIVER_SEQ1";
  DROP SEQUENCE "HDCP1"."CASEWORKER_SEQ1";
  DROP SEQUENCE "HDCP1"."INVOICE_SEQ1";
--------------------------------------------------------
--  DDL for Sequence CAREGIVER_SEQ1
--------------------------------------------------------

   CREATE SEQUENCE  "HDCP1"."CAREGIVER_SEQ1"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1020 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence CASEWORKER_SEQ1
--------------------------------------------------------

   CREATE SEQUENCE  "HDCP1"."CASEWORKER_SEQ1"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 2020 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence INVOICE_SEQ1
--------------------------------------------------------

   CREATE SEQUENCE  "HDCP1"."INVOICE_SEQ1"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 3020 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Table CAREGIVER
--------------------------------------------------------

  CREATE TABLE "HDCP1"."CAREGIVER" 
   (	"CAREGIVERID" NUMBER, 
	"FIRSTNAME" VARCHAR2(30 BYTE), 
	"MIDDLEINIT" CHAR(1 BYTE), 
	"LASTNAME" VARCHAR2(30 BYTE), 
	"STREET" VARCHAR2(30 BYTE), 
	"CITY" VARCHAR2(30 BYTE), 
	"STATE" VARCHAR2(30 BYTE), 
	"SEX" CHAR(1 BYTE), 
	"EMAIL" VARCHAR2(30 BYTE), 
	"DATEOFBIRTH" DATE, 
	"PHONE" VARCHAR2(30 BYTE), 
	"ZIPCODE" VARCHAR2(30 BYTE), 
	"HOURLYPAYRATE" NUMBER(6,2)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Table CASEWORKER
--------------------------------------------------------

  CREATE TABLE "HDCP1"."CASEWORKER" 
   (	"CASEWORKERID" NUMBER, 
	"FIRSTNAME" VARCHAR2(30 BYTE), 
	"MIDDLEINIT" CHAR(1 BYTE), 
	"LASTNAME" VARCHAR2(30 BYTE), 
	"STREET" VARCHAR2(30 BYTE), 
	"CITY" VARCHAR2(30 BYTE), 
	"STATE" VARCHAR2(30 BYTE), 
	"SEX" CHAR(1 BYTE), 
	"PHONE" VARCHAR2(20 BYTE), 
	"ZIPCODE" VARCHAR2(30 BYTE), 
	"NAMEOFINSURER" VARCHAR2(30 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Table CLIENT
--------------------------------------------------------

  CREATE TABLE "HDCP1"."CLIENT" 
   (	"CLIENTSSN" VARCHAR2(9 BYTE), 
	"FIRSTNAME" VARCHAR2(30 BYTE), 
	"MIDDLEINIT" CHAR(1 BYTE), 
	"LASTNAME" VARCHAR2(30 BYTE), 
	"STREET" VARCHAR2(30 BYTE), 
	"CITY" VARCHAR2(30 BYTE), 
	"STATE" VARCHAR2(30 BYTE), 
	"PHONE" VARCHAR2(30 BYTE), 
	"SEX" CHAR(1 BYTE), 
	"EMAIL" VARCHAR2(30 BYTE), 
	"ZIPCODE" VARCHAR2(30 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Table CLIENTCONTACT
--------------------------------------------------------

  CREATE TABLE "HDCP1"."CLIENTCONTACT" 
   (	"CLIENTSSN" VARCHAR2(9 BYTE), 
	"FIRSTNAME" VARCHAR2(30 BYTE), 
	"LASTNAME" VARCHAR2(30 BYTE), 
	"STREET" VARCHAR2(30 BYTE), 
	"CITY" VARCHAR2(30 BYTE), 
	"CONTACTSTATE" VARCHAR2(30 BYTE), 
	"CONTACTSEX" CHAR(1 BYTE), 
	"PHONE" VARCHAR2(30 BYTE), 
	"RELATIONSHIP" VARCHAR2(30 BYTE), 
	"ZIPCODE" VARCHAR2(30 BYTE), 
	"STATE" VARCHAR2(30 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Table CREDENTIAL
--------------------------------------------------------

  CREATE TABLE "HDCP1"."CREDENTIAL" 
   (	"CREDENTIALTYPE" VARCHAR2(30 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Table INSURED
--------------------------------------------------------

  CREATE TABLE "HDCP1"."INSURED" 
   (	"CLIENTSSN" VARCHAR2(9 BYTE), 
	"CASEWORKERID" NUMBER, 
	"INSURANCENUMBER" VARCHAR2(30 BYTE), 
	"ISPRIMARY" CHAR(1 BYTE), 
	"HOURLYBILLRATE" NUMBER(6,2)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Table INVOICE
--------------------------------------------------------

  CREATE TABLE "HDCP1"."INVOICE" 
   (	"INVOICENUMBER" NUMBER(15,0), 
	"DOLLARAMOUNT" NUMBER(10,2), 
	"INVOICEDATE" DATE, 
	"CLIENTSSN" VARCHAR2(9 BYTE), 
	"CASEWORKERID" NUMBER
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Table PAYMENT
--------------------------------------------------------

  CREATE TABLE "HDCP1"."PAYMENT" 
   (	"INVOICENUMBER" NUMBER, 
	"PAYMENTAMOUNT" NUMBER(10,2), 
	"PAYMENTDATE" DATE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Table SCHEDULEDVISIT
--------------------------------------------------------

  CREATE TABLE "HDCP1"."SCHEDULEDVISIT" 
   (	"CLIENTSSN" VARCHAR2(9 BYTE), 
	"DATEOFVISIT" DATE, 
	"CASEWORKERID" NUMBER, 
	"TYPEOFCARE" VARCHAR2(30 BYTE), 
	"TIMESTARTED" DATE, 
	"TIMEENDED" DATE, 
	"INVOICENUMBER" NUMBER
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Table STAFFCONTACT
--------------------------------------------------------

  CREATE TABLE "HDCP1"."STAFFCONTACT" 
   (	"CAREGIVERID" NUMBER, 
	"FIRSTNAME" VARCHAR2(30 BYTE), 
	"LASTNAME" VARCHAR2(30 BYTE), 
	"MIDDLEINIT" CHAR(1 BYTE), 
	"STREET" VARCHAR2(30 BYTE), 
	"CITY" VARCHAR2(30 BYTE), 
	"CONTACTSTATE" VARCHAR2(30 BYTE), 
	"PHONE" NUMBER(30,0), 
	"SEX" CHAR(1 BYTE), 
	"ZIPCODE" VARCHAR2(30 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Table STAFFCREDENTIAL
--------------------------------------------------------

  CREATE TABLE "HDCP1"."STAFFCREDENTIAL" 
   (	"CAREGIVERID" NUMBER, 
	"EXPIRATIONDATE" DATE, 
	"IDENTIFICATIONNUMBER" VARCHAR2(30 BYTE), 
	"CREDENTIALTYPE" VARCHAR2(30 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
REM INSERTING into HDCP1.CAREGIVER
SET DEFINE OFF;
Insert into HDCP1.CAREGIVER (CAREGIVERID,FIRSTNAME,MIDDLEINIT,LASTNAME,STREET,CITY,STATE,SEX,EMAIL,DATEOFBIRTH,PHONE,ZIPCODE,HOURLYPAYRATE) values (1000,'Doug','J','Keeley','1000 Fleet St','Baltimore','MD','M','doug@doug.com',null,'410-555-1212','21202',200);
REM INSERTING into HDCP1.CASEWORKER
SET DEFINE OFF;
Insert into HDCP1.CASEWORKER (CASEWORKERID,FIRSTNAME,MIDDLEINIT,LASTNAME,STREET,CITY,STATE,SEX,PHONE,ZIPCODE,NAMEOFINSURER) values (2000,'Julia',null,'Childs','4000 Broadway','New York','NY','F','212-555-1212','1002','Medicaid');
REM INSERTING into HDCP1.CLIENT
SET DEFINE OFF;
Insert into HDCP1.CLIENT (CLIENTSSN,FIRSTNAME,MIDDLEINIT,LASTNAME,STREET,CITY,STATE,PHONE,SEX,EMAIL,ZIPCODE) values ('111223333','Brian','R','McWhomever','100 Disabled Alley','Baltimore','MD','410-555-1234',null,'brian@mcwhomever.com','21214');
REM INSERTING into HDCP1.CLIENTCONTACT
SET DEFINE OFF;
REM INSERTING into HDCP1.CREDENTIAL
SET DEFINE OFF;
REM INSERTING into HDCP1.INSURED
SET DEFINE OFF;
Insert into HDCP1.INSURED (CLIENTSSN,CASEWORKERID,INSURANCENUMBER,ISPRIMARY,HOURLYBILLRATE) values ('111223333',2000,'876543543','Y',100);
REM INSERTING into HDCP1.INVOICE
SET DEFINE OFF;
Insert into HDCP1.INVOICE (INVOICENUMBER,DOLLARAMOUNT,INVOICEDATE,CLIENTSSN,CASEWORKERID) values (454322980,1975.25,to_date('31-JUL-10','DD-MON-RR'),null,null);
Insert into HDCP1.INVOICE (INVOICENUMBER,DOLLARAMOUNT,INVOICEDATE,CLIENTSSN,CASEWORKERID) values (3000,200,to_date('03-DEC-11','DD-MON-RR'),'111223333',2000);
REM INSERTING into HDCP1.PAYMENT
SET DEFINE OFF;
Insert into HDCP1.PAYMENT (INVOICENUMBER,PAYMENTAMOUNT,PAYMENTDATE) values (3000,200,to_date('05-DEC-11','DD-MON-RR'));
REM INSERTING into HDCP1.SCHEDULEDVISIT
SET DEFINE OFF;
REM INSERTING into HDCP1.STAFFCONTACT
SET DEFINE OFF;
REM INSERTING into HDCP1.STAFFCREDENTIAL
SET DEFINE OFF;
--------------------------------------------------------
--  DDL for Index CAREGIVER_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "HDCP1"."CAREGIVER_PK" ON "HDCP1"."CAREGIVER" ("CAREGIVERID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Index CASEWORKER_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "HDCP1"."CASEWORKER_PK" ON "HDCP1"."CASEWORKER" ("CASEWORKERID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Index CLIENT_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "HDCP1"."CLIENT_PK" ON "HDCP1"."CLIENT" ("CLIENTSSN") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Index CREDENTIAL_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "HDCP1"."CREDENTIAL_PK" ON "HDCP1"."CREDENTIAL" ("CREDENTIALTYPE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Index INSURED_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "HDCP1"."INSURED_PK" ON "HDCP1"."INSURED" ("CLIENTSSN", "CASEWORKERID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Index INVOICE_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "HDCP1"."INVOICE_PK" ON "HDCP1"."INVOICE" ("INVOICENUMBER") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  DDL for Index SCHEDULEDVISIT_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "HDCP1"."SCHEDULEDVISIT_PK" ON "HDCP1"."SCHEDULEDVISIT" ("CLIENTSSN", "DATEOFVISIT") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;
--------------------------------------------------------
--  Constraints for Table CAREGIVER
--------------------------------------------------------

  ALTER TABLE "HDCP1"."CAREGIVER" ADD CONSTRAINT "CAREGIVER_PK" PRIMARY KEY ("CAREGIVERID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE;
--------------------------------------------------------
--  Constraints for Table CASEWORKER
--------------------------------------------------------

  ALTER TABLE "HDCP1"."CASEWORKER" MODIFY ("CASEWORKERID" NOT NULL ENABLE);
  ALTER TABLE "HDCP1"."CASEWORKER" ADD CONSTRAINT "CASEWORKER_PK" PRIMARY KEY ("CASEWORKERID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE;
--------------------------------------------------------
--  Constraints for Table CLIENT
--------------------------------------------------------

  ALTER TABLE "HDCP1"."CLIENT" ADD CONSTRAINT "CLIENT_PK" PRIMARY KEY ("CLIENTSSN")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE;
  ALTER TABLE "HDCP1"."CLIENT" MODIFY ("CLIENTSSN" NOT NULL ENABLE);

--------------------------------------------------------
--  Constraints for Table CREDENTIAL
--------------------------------------------------------

  ALTER TABLE "HDCP1"."CREDENTIAL" MODIFY ("CREDENTIALTYPE" NOT NULL ENABLE);
  ALTER TABLE "HDCP1"."CREDENTIAL" ADD CONSTRAINT "CREDENTIAL_PK" PRIMARY KEY ("CREDENTIALTYPE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE;
--------------------------------------------------------
--  Constraints for Table INSURED
--------------------------------------------------------

  ALTER TABLE "HDCP1"."INSURED" MODIFY ("CLIENTSSN" NOT NULL ENABLE);
  ALTER TABLE "HDCP1"."INSURED" MODIFY ("CASEWORKERID" NOT NULL ENABLE);
  ALTER TABLE "HDCP1"."INSURED" ADD CONSTRAINT "INSURED_PK" PRIMARY KEY ("CLIENTSSN", "CASEWORKERID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE;
--------------------------------------------------------
--  Constraints for Table INVOICE
--------------------------------------------------------

  ALTER TABLE "HDCP1"."INVOICE" ADD CONSTRAINT "INVOICE_PK" PRIMARY KEY ("INVOICENUMBER")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE;
  ALTER TABLE "HDCP1"."INVOICE" MODIFY ("INVOICENUMBER" NOT NULL ENABLE);

--------------------------------------------------------
--  Constraints for Table SCHEDULEDVISIT
--------------------------------------------------------

  ALTER TABLE "HDCP1"."SCHEDULEDVISIT" MODIFY ("CLIENTSSN" NOT NULL ENABLE);
  ALTER TABLE "HDCP1"."SCHEDULEDVISIT" MODIFY ("DATEOFVISIT" NOT NULL ENABLE);
  ALTER TABLE "HDCP1"."SCHEDULEDVISIT" ADD CONSTRAINT "SCHEDULEDVISIT_PK" PRIMARY KEY ("CLIENTSSN", "DATEOFVISIT")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE;

--------------------------------------------------------
--  Constraints for Table STAFFCREDENTIAL
--------------------------------------------------------

  ALTER TABLE "HDCP1"."STAFFCREDENTIAL" MODIFY ("CAREGIVERID" NOT NULL ENABLE);



--------------------------------------------------------
--  Ref Constraints for Table CLIENTCONTACT
--------------------------------------------------------

  ALTER TABLE "HDCP1"."CLIENTCONTACT" ADD CONSTRAINT "CLIENTCONTACT_FK1" FOREIGN KEY ("CLIENTSSN")
	  REFERENCES "HDCP1"."CLIENT" ("CLIENTSSN") ENABLE;

--------------------------------------------------------
--  Ref Constraints for Table INSURED
--------------------------------------------------------

  ALTER TABLE "HDCP1"."INSURED" ADD CONSTRAINT "INSURED_FK1" FOREIGN KEY ("CLIENTSSN")
	  REFERENCES "HDCP1"."CLIENT" ("CLIENTSSN") ENABLE;
  ALTER TABLE "HDCP1"."INSURED" ADD CONSTRAINT "INSURED_FK2" FOREIGN KEY ("CASEWORKERID")
	  REFERENCES "HDCP1"."CASEWORKER" ("CASEWORKERID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table INVOICE
--------------------------------------------------------

  ALTER TABLE "HDCP1"."INVOICE" ADD CONSTRAINT "INVOICE_FK1" FOREIGN KEY ("CLIENTSSN")
	  REFERENCES "HDCP1"."CLIENT" ("CLIENTSSN") ENABLE;
  ALTER TABLE "HDCP1"."INVOICE" ADD CONSTRAINT "INVOICE_FK2" FOREIGN KEY ("CASEWORKERID")
	  REFERENCES "HDCP1"."CASEWORKER" ("CASEWORKERID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table PAYMENT
--------------------------------------------------------

  ALTER TABLE "HDCP1"."PAYMENT" ADD CONSTRAINT "PAYMENT_FK1" FOREIGN KEY ("INVOICENUMBER")
	  REFERENCES "HDCP1"."INVOICE" ("INVOICENUMBER") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table SCHEDULEDVISIT
--------------------------------------------------------

  ALTER TABLE "HDCP1"."SCHEDULEDVISIT" ADD CONSTRAINT "SCHEDULEDVISIT_FK1" FOREIGN KEY ("CLIENTSSN")
	  REFERENCES "HDCP1"."CLIENT" ("CLIENTSSN") ENABLE;
  ALTER TABLE "HDCP1"."SCHEDULEDVISIT" ADD CONSTRAINT "SCHEDULEDVISIT_FK2" FOREIGN KEY ("CASEWORKERID")
	  REFERENCES "HDCP1"."CAREGIVER" ("CAREGIVERID") ENABLE;
  ALTER TABLE "HDCP1"."SCHEDULEDVISIT" ADD CONSTRAINT "SCHEDULEDVISIT_INVOICE_FK1" FOREIGN KEY ("INVOICENUMBER")
	  REFERENCES "HDCP1"."INVOICE" ("INVOICENUMBER") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table STAFFCONTACT
--------------------------------------------------------

  ALTER TABLE "HDCP1"."STAFFCONTACT" ADD CONSTRAINT "STAFFCONTACT_FK1" FOREIGN KEY ("CAREGIVERID")
	  REFERENCES "HDCP1"."CAREGIVER" ("CAREGIVERID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table STAFFCREDENTIAL
--------------------------------------------------------

  ALTER TABLE "HDCP1"."STAFFCREDENTIAL" ADD CONSTRAINT "STAFFCREDENTIAL_FK1" FOREIGN KEY ("CREDENTIALTYPE")
	  REFERENCES "HDCP1"."CREDENTIAL" ("CREDENTIALTYPE") ENABLE;
--------------------------------------------------------
--  DDL for Trigger CAREGIVER_T1
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "HDCP1"."CAREGIVER_T1" 
BEFORE
insert on CareGiver
for each row
begin
if :NEW.careGiverId is null then 
    select CAREGIVER_SEQ1.nextval into :NEW.careGiverId from dual; 
  end if; 
end;
/
ALTER TRIGGER "HDCP1"."CAREGIVER_T1" ENABLE;
--------------------------------------------------------
--  DDL for Trigger CASEWORKER_T1
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "HDCP1"."CASEWORKER_T1" 
BEFORE
insert on CaseWorker
for each row
begin
  if :NEW.caseWorkerId is null then 
    select CASEWORKER_SEQ1.nextval into :NEW.caseWorkerId from dual; 
  end if;
end;
/
ALTER TRIGGER "HDCP1"."CASEWORKER_T1" ENABLE;
--------------------------------------------------------
--  DDL for Trigger INVOICE_T1
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "HDCP1"."INVOICE_T1" 
BEFORE
insert on Invoice
for each row
begin
  if :NEW.invoiceNumber is null then 
    select INVOICE_SEQ1.nextval into :NEW.invoiceNumber from dual; 
  end if; 
end;
/
ALTER TRIGGER "HDCP1"."INVOICE_T1" ENABLE;
